package wili.sql;

import java.io.File;
import java.io.FileOutputStream;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.IOUtils;

import wili.c.Ca;
import wili.c.File9a;

/**
 * @author weila 2022年7月23日
 */
class Coms {
	public static String strOfMapValues(int firstLineIsKeys, Map<String, Object> map, List<String> keys, int[] wids) {
		StringBuilder sb = new StringBuilder();
		if (firstLineIsKeys == 1) {
			sb.append("\n");
			for (int k = 0; k < keys.size(); k++) {
				int wid = wids[k];
				String st = String.format("%1$-" + wid + "s", keys.get(k));
				sb.append(st);
			}
		}
		sb.append("\n");
		for (int k = 0; k < keys.size(); k++) {
			int wid = wids[k];
			String cname = keys.get(k);
			Object va = map.get(cname);
			String st;
			if (va == null) {
				st = "-";
			} else if (va instanceof java.sql.Clob) {
				st = "Clob...";
			} else {
				st = va.toString().replace("\n", "/n").replace("\r", "/r");
			}
			st = String.format("%1$-" + wid + "s", st);
			sb.append(st);
		}
		String st = sb.toString();
		return st;
	}

	public static int[] columnWidths_of_mapList(List<Map<String, Object>> map, List<String> colNames) {
		int[] wids = new int[colNames.size()];
		for (int i = 0; i < colNames.size(); i++) {
			int max = colNames.get(i).length();
			for (Map<String, Object> row : map) {
				Object cell = row.get(colNames.get(i));
				if (cell instanceof java.sql.Clob)
					cell = "Clob...";
				int width = cell == null ? 1 : cell.toString().length();
				if (width > max)
					max = width;
			}
			wids[i] = max + 2;
		}
		return wids;
	}

	public static Map<String, Object> nextDataMapOf(ResultSet rs, List<String> cols) {
		try {
			if (!rs.next())
				return null;
			Map<String, Object> ret = new HashMap<>();
			for (String col : cols) {
				Object obj = rs.getObject(col);
				ret.put(col, obj);
			}
			return ret;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static List<String> findTableNames(String db, String tableNamePat, Connection conn) {
		try {
			DatabaseMetaData meta = conn.getMetaData();
			if (db.isEmpty())
				conn.getCatalog();
			String cat = conn.getCatalog(), schema = conn.getSchema();
			String[] tt = new String[] { "TABLE" };
			ResultSet res = meta.getTables(cat, null, null, null);
			List<String> list = new ArrayList<String>();
			while (res.next()) {
				String typ = res.getString("TABLE_TYPE");
				String tableName = res.getString("TABLE_NAME");
				if (tableName.contains("@"))
					continue;
				if (tableName.matches(tableNamePat)) {
					list.add(tableName);
				}
			}
			Collections.sort(list);
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public static Integer intOf(ResultSet rs) {
		try {
			rs.next();
			return rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static void test_readAll_tableContent_to_textFile(Connection conn) {
		//File dir = new File("D:\\bearJ\\ruleMiner2022a\\temp");
		File dir = new File("C:\\Perforce\\1810399\\BUSMB_B1\\SBO\\dev\\Source\\WebClient\\java\\t\\table_textContext");
		readAll_tableContent_to_textFile(dir, conn);
	}

	public static List<String> getTables_b1() {
		return File9a.strListOfFile(new File("C:\\Perforce\\tableNames.txt"));
	}

	public static void readAll_tableContent_to_textFile(File toDir, Connection conn) {
		try {
			String db = conn.getCatalog();
			if (db == null)
				db = "DC1";
			conn.getSchema();
			List<String> list = null;
			int opt = 2;
			if (opt == 1) {
				list = Arrays.asList(
						"DLN1,DLN4,INV1,INV4,JDT1,ODLN,OILM,OINM,OINV,OIVL,OJDT,OPCH,OPDN,OPOR,OQUT,ORDR,PCH1,PCH4,PDN1,PDN4,POR1,POR4,QUT1,QUT4,RDR1,RDR4,RITM"
								.split(","));
			} else if (opt == 2) {
				list = getTables_b1();
			} else {
				list = Coms.findTableNames(db, null, conn);
			}
			conn.setCatalog("SBODEMOUS");
			for (int i = 0, j = 0, k = 0; i < list.size(); i++) {
				String tab = list.get(i);
				File file = new File(toDir, tab + ".txt");
				if (file.exists())
					continue;
				if (readTableContent_to_textFile(db, tab, file, conn)) {
					log(i + ", readTableContent_to_textFile: " + file.getName() + ", " + (i + 1) + "/" + list.size());
					//break;
				} else {
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static boolean readTableContent_to_textFile(String db, String tab, File toFile, Connection conn) {
		String sql;
		try {
			int maxRow = 100;
			//conn.createStatement().execute("");
			String pdName = conn.getMetaData().getDatabaseProductName();
			if ("456".isEmpty() || pdName.equals("MySQL")) {
				conn.createStatement().execute("use " + db);
			}
			//conn.getCatalog()
			String catalog = "SBODEMOUS.";
			ResultSet rs = conn.createStatement().executeQuery("select count(*) from " + catalog + tab);
			int dataNum = Coms.intOf(rs);
			if (dataNum == 0) {
				//log(" Table is empty: " + tab);
				return false;
			}
			String msg = "Date: " + new java.util.Date().toLocaleString();
			if (dataNum > maxRow) {
				msg = msg + ", list first " + maxRow + " rows in " + dataNum;
				log(msg);
				dataNum = maxRow;
			}
			sql = "select top 1 * from " + catalog + tab;
			rs = conn.createStatement().executeQuery(sql);
			String firstCol = Sql9.getColumn(rs, "DocEntry");
			if (firstCol == null) {
				firstCol = Sql9.getColumn(rs, 1);
			}
			sql = "select top " + maxRow + " * from " + catalog + tab + " order by \"" + firstCol + "\" desc";
			Ca.log("readTableContent, sql:" + sql);
			rs = conn.createStatement().executeQuery(sql);
			List<String> colNames = Sql9.columnNameListOf(rs, true);
			List<Map<String, Object>> datas = new ArrayList<>(dataNum);
			for (int k = 0; k < dataNum; k++) {
				Map<String, Object> map = Coms.nextDataMapOf(rs, colNames);
				datas.add(map);
			}
			int[] wids = Coms.columnWidths_of_mapList(datas, colNames);
			toFile.createNewFile();
			FileOutputStream out = new FileOutputStream(toFile);
			StringBuilder sb = new StringBuilder(datas.size() * 20);
			sb.append(msg);
			int ind = 0, length = 0;
			for (int i = 0; i < datas.size(); i++) {
				Map<String, Object> map = datas.get(i);
				++ind;
				String line = Coms.strOfMapValues(ind, map, colNames, wids);
				sb.append(line);
				length += line.length();
				if (length > Integer.MAX_VALUE / 2)
					"".isEmpty();
			}
			IOUtils.write(sb.toString(), out, Charset.defaultCharset());
			out.close();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			log(tab + ", " + e.toString());
			return false;
		}
	}

	static void log(Object msg) {
		System.out.println(msg);
	}
}
